package com.example.demo.repository;

import com.example.demo.entity.Appointment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment,Integer> {
    @Query(value = "SELECT a from Appointment a where a.id=:id and a.state=1")
     Appointment findByIds(@Param("id")Integer id);

    @Query(value = "SELECT a.id,u.id AS uid,u.username,u.mobile,o.outpatientname,o.outpatientid,a.date,a.doctor AS doctorid,a.state,a._live,u1.`username` AS doctorname FROM  Appointment a LEFT JOIN Users u ON a.`u_id`=u.id LEFT JOIN Outpatient o ON a.outpatientid=o.outpatientid LEFT JOIN  users u1 ON a.`doctor`=u1.`id` WHERE u.`type`='user'  AND a.`_live`=1 AND o.`_live`=1 AND u.`_live`=1 and u.id=:id",nativeQuery = true)
    List<Map<String,Object>> query(@Param("id") Integer id);

    @Query(value = "SELECT u.id as u_id,u.username,u.mobile,o.outpatientname,o.outpatientid,a.date,a.doctor AS doctorid,a.state,a._live,u1.`username` AS doctorname FROM  Appointment a LEFT JOIN Users u ON a.`u_id`=u.id LEFT JOIN Outpatient o ON a.outpatientid=o.outpatientid LEFT JOIN  users u1 ON a.`doctor`=u1.`id` WHERE u.`type`='user'  AND a.`_live`=1 AND o.`_live`=1 AND u.`_live`=1 order by a.date desc limit :page,:pageSize",nativeQuery = true)
    List<Map<String,Object>> find(@Param("page")Integer page,@Param("pageSize")Integer pageSize);

    @Query(value = "SELECT a.doctor as id,u.id as u_id,u.username,u.mobile,o.outpatientname,o.outpatientid,a.date,a.doctor AS doctorid,a.state,a._live,u1.`username` AS doctorname FROM  Appointment a LEFT JOIN Users u ON a.`u_id`=u.id LEFT JOIN Outpatient o ON a.outpatientid=o.outpatientid LEFT JOIN  users u1 ON a.`doctor`=u1.`id` WHERE u.`type`='user'  AND a.`_live`=1 AND o.`_live`=1 AND u.`_live`=1 and a.doctor=:id order by a.date desc limit :page,:pageSize",nativeQuery = true)
    List<Map<String,Object>> findByType(@Param("page")Integer page,@Param("pageSize")Integer pageSize,@Param("id")Integer id);

    @Query(value = "select count(t.id) from (SELECT u.id,u.username,u.mobile,o.outpatientname,o.outpatientid,a.date,a.doctor AS doctorid,a.state,a._live,a.u_id ,u1.`username` AS doctorname FROM  Appointment a LEFT JOIN Users u ON a.`u_id`=u.id LEFT JOIN Outpatient o ON a.outpatientid=o.outpatientid LEFT JOIN  users u1 ON a.`doctor`=u1.`id` WHERE u.`type`='user'  AND a.`_live`=1 AND o.`_live`=1 AND u.`_live`=1) as t",nativeQuery = true)
    int total();

    @Query(value = "SELECT a.id,a.doctor,u.id as u_id,u.username,u.mobile,o.outpatientname,o.outpatientid,a.date,a.doctor AS doctorid,a.state,a._live,u1.`username` AS doctorname FROM  Appointment a LEFT JOIN Users u ON a.`u_id`=u.id LEFT JOIN Outpatient o ON a.outpatientid=o.outpatientid LEFT JOIN  users u1 ON a.`doctor`=u1.`id` WHERE u.`type`='user'  AND a.`_live`=1 AND o.`_live`=1 AND u.`_live`=1 and a.state=1 order by a.date desc limit :page,:pageSize",nativeQuery = true)
    List<Map<String,Object>> findById1(@Param("page")Integer page,@Param("pageSize")Integer pageSize);

}
